/**   
 * @Title: JdbcDaoImpl.java 
 * CopyRight(c)2010-DMX 
 */
package com.dmx.fw.dao.hibernateimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.dmx.fw.constant.SystemConstant;
import com.dmx.fw.dao.interfaces.JdbcDao;
import com.dmx.fw.util.DateTimeUtils;

/**
 * @Description: 继承BaseDaoImpl,实现JdbcDao,基本sql查询、执行
 * @author: system
 * @date: 2006
 * @version V1.0 
 * 
 */
public class JdbcDaoImpl extends BaseDaoImpl implements JdbcDao {
	private final static Logger log = Logger.getLogger(JdbcDaoImpl.class);

	/**
	 * get data from the ResultSet which is save in the given colnum and type
	 * 
	 * @param rs
	 * @param colNum
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	private Object getValue(final ResultSet rs, int colNum, int type)
			throws SQLException {
		switch (type) {
			case Types.ARRAY:
			case Types.BLOB:
			case Types.CLOB:
			case Types.DISTINCT:
			case Types.LONGVARBINARY:
			case Types.VARBINARY:
			case Types.BINARY:
			case Types.REF:
			case Types.STRUCT:
				return null;
			default: {
				Object value = rs.getObject(colNum);
				if (rs.wasNull() || (value == null))
					return null;
				else
					return value;
			}
		}
	}
	
	/**
	 * change a ResultSet to ArrayList if the data in ResultSet is null then
	 * save it as a default string
	 * 
	 * @param resultSet
	 * @param strDefalut
	 * @param transType  "MAP"-every record translate to HashMap,"LIST"-every record translate to ArrayList
	 * @return
	 * @throws SQLException
	 */
	private ArrayList FromResultToArrayList(ResultSet resultSet,	String strDefalut,String transType) {
		ArrayList dataList = new ArrayList();
		
		if (resultSet == null)
			return dataList;
		try {

			// get the strut of the ResultSet
			ResultSetMetaData resultmd = resultSet.getMetaData();
			if (resultmd == null)
				return dataList;

			// get the total count of column
			int colCount = resultmd.getColumnCount();

			// deal with all data one by one		
			while (resultSet.next()) {
				Object row = null;
				if ("MAP".equalsIgnoreCase(transType)){
					row = new HashMap();
				}else{
					row = new ArrayList(colCount);
				}				
				
				for (int i = 1; i <= colCount; i++) {
					// get type of column
					int type  = resultmd.getColumnType(i);
					String colName = resultmd.getColumnName(i);
					// get data
					if ("MAP".equalsIgnoreCase(transType)){
						((HashMap)row).put(colName.toUpperCase(), ObjectToString(getValue(resultSet, i, type),
							strDefalut));
					}else{
						((ArrayList)row).add(ObjectToString(getValue(resultSet, i, type),
							strDefalut));
					}
				}
				dataList.add(row);
			}
		} catch (SQLException e) {
			log.error(e);
		} 
		return dataList;
	}
	
	/**
	 * change the object to String type
	 * 
	 * @param obj
	 * @param strDefalut
	 * @return
	 */
	private String ObjectToString(Object obj, String strDefalut) {
		if (obj == null)
			return strDefalut;

		String strResult = strDefalut;
		// date type java.sql.Date
		if (obj instanceof java.sql.Date) {
			strResult = DateTimeUtils.DateToString((java.sql.Date) obj,
					SystemConstant.CURR_JAVA_DATE_FORMATTER, strDefalut);
			// date type java.sql.T
		} else if (obj instanceof Timestamp) {
			strResult = DateTimeUtils.TimestampToString(
					(java.sql.Timestamp) obj,
					SystemConstant.CURR_JAVA_DATETIME_FORMATTER, strDefalut);
			// numeral type
		} else if (obj instanceof java.math.BigDecimal) {
			strResult = ((java.math.BigDecimal) obj).toString();
		} else {
			strResult = obj.toString();
		}

		if (strResult != null)
			strResult = strResult.trim();
		return strResult;
	}

	private String ObjectToString(Object obj) {
		return ObjectToString(obj, "");
	}

	/**
	 * 从数据库获取查询结果
	 * @param sql
	 * @param encapType
	 * @return
	 * @see queryData(final String sql,final String encapType,final List params)
	 */
	public List queryData(final String sql,final String encapType){		
		return queryData(sql,encapType,null);	
	}
	
	/**
	 * 从数据库获取查询结果
	 * @param sql
	 * @param encapType: 查询结果封装方式MAP/LIST
	 * @param params: 参数列表
	 * @return
	 */
	public List queryData(final String sql,final String encapType,final List params){		
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
            	PreparedStatement pst = null;
        		ResultSet rs = null;
        		ArrayList resultList = null;
        		Connection conn = null;
        		try {
        			conn = session.connection();
        			pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
        					ResultSet.CONCUR_READ_ONLY);
        			
        			if(params!=null){
        				for (int i = 0; i<params.size(); i++){
        					Object obj = params.get(i);
        					if (obj instanceof Integer){
        						pst.setInt(i+1, ((Integer)obj).intValue());
        					}else if (obj instanceof Long){
        						pst.setLong(i+1, ((Long)obj).longValue());
        					}else if (obj instanceof Double){
        						pst.setDouble(i+1, ((Double)obj).doubleValue());
        					}else if (obj instanceof Float){
        						pst.setFloat(i+1, ((Float)obj).floatValue());
        					}else if (obj instanceof java.util.Date){
        						pst.setDate(i+1, (java.sql.Date)obj);
        					}else if (obj instanceof java.sql.Date){
        						pst.setDate(i+1, (java.sql.Date)obj);
        					}else if (obj instanceof String){
        						pst.setString(i+1, (String)obj);
        					}else{
        						pst.setObject(i+1, obj);
        					}
        				}
        			}
        			
        			rs = pst.executeQuery();
        			resultList = FromResultToArrayList(rs,SystemConstant.DEFAULTNNULLVAL,encapType);

        		} catch (SQLException e) {
        			log.error(e);
        		} finally {
        			closeResultSet(rs);
        			closeStatement(pst);
        		}
        		return resultList;
            }
        });		
	}
	
	
	
	public List queryData(String sql){
		return queryData(sql,"LIST");
	}
	
	public List queryDataOfMapEncap(String sql){
		return queryData(sql,"MAP");
	}

	/**
	 * 获得查询的结果总数
	 * @param conn
	 * @param originalSql
	 * @return
	 */
	public int getTotalCount(final String sql){
		
		Integer iResult =  (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
        		Connection conn = null;
        		int iRes = 0;
        		ResultSet rs = null;
        		PreparedStatement pst = null;
        		String newSQL = "SELECT COUNT(*) FROM (" + sql + ") a";
        		try {
        			conn = session.connection();
        			pst = conn.prepareStatement(newSQL,
        					ResultSet.TYPE_SCROLL_INSENSITIVE,
        					ResultSet.CONCUR_READ_ONLY);
        			rs = pst.executeQuery();
        			if (rs.next())
        				iRes = rs.getInt(1);
        		} catch (SQLException e) {
        			log.error(e);
        		} finally {
        			closeResultSet(rs);
        			closeStatement(pst);
        		}
        		return new Integer(iRes);
            }
        });
		
		return iResult.intValue();
	}

	protected void closeResultSet(ResultSet result) {
		if (result != null) {
			try {
				result.close();
			} catch (Exception e) {
			}
		}
		result = null;
	}

	protected void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {

			}
		}
		st = null;
	}
	
	protected void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {

			}
		}
		conn = null;
	}
	
	public int execute(final String sql){		
		Integer iResult =  (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
            	int returnVal = 0;            	
            	PreparedStatement statement = null;
            	try{
	        		Connection conn = session.connection();
	        		statement = conn.prepareStatement(sql);
	    		    returnVal = statement.executeUpdate();
	        		return new Integer(returnVal);
            	}finally{
            		closeStatement(statement);
            	}
            }
        });		
		return iResult.intValue();
		
	}
}
